package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bfc.BFCMessage;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.bpm.BPMTaskBase;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.wms.*;
import com.iplant.aps.service.utils.CalendarUtil;
import com.iplant.aps.service.utils.CloneTool;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.CoreServiceImpl;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.dao.TaskBaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class MESMaterialReturnDAO extends BaseDAO implements TaskBaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(MESMaterialReturnDAO.class);

    private static MESMaterialReturnDAO Instance = null;

    private MESMaterialReturnDAO() {
        super();
    }

    public static MESMaterialReturnDAO getInstance() {
        if (Instance == null)
            Instance = new MESMaterialReturnDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public MESMaterialReturn Update(BMSEmployee wLoginUser, MESMaterialReturn wMESMaterialReturn,
                                    OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wMESMaterialReturn;
            }

            if (wMESMaterialReturn == null)
                return null;

            String wSQL = "";
            if (wMESMaterialReturn.getID() <= 0) {
                wSQL = MessageFormat.format(
                        "INSERT INTO {0}.mes_materialreturn(Code,UpFlowID,FlowType,FlowID,FollowerID,"
                                + "Factory,AsnType,CreateTime,CreateTime1,MonitorNo,Monitor,ProductID,"
                                + "LineID,CustomerID,OrderID,PartID,Status,PartNo,Remark,BackTime,SubmitTime," +
                                "StatusText,SendStatus,SendDesc,Type,DemandID,TechChangeID,BOMType) "
                                + "VALUES(:Code,:UpFlowID,:FlowType,:FlowID,:FollowerID,:Factory,:AsnType,"
                                + ":CreateTime,:CreateTime1,:MonitorNo,:Monitor,:ProductID,:LineID,:CustomerID,"
                                + ":OrderID,:PartID,:Status,:PartNo,:Remark,:BackTime,:SubmitTime,:StatusText," +
                                ":SendStatus,:SendDesc,:Type,:DemandID,:TechChangeID,:BOMType);", wInstance.Result);
                wMESMaterialReturn.UpFlowID = wLoginUser.ID;
            } else {
                wSQL = MessageFormat.format(
                        "UPDATE {0}.mes_materialreturn SET Code = :Code,UpFlowID = :UpFlowID,FlowType = :FlowType,"
                                + "FlowID = :FlowID,FollowerID = :FollowerID,Factory = :Factory,AsnType = :AsnType,"
                                + "CreateTime = :CreateTime,CreateTime1 = :CreateTime1,MonitorNo = :MonitorNo,"
                                + "Monitor = :Monitor,ProductID = :ProductID,LineID = :LineID,CustomerID = :CustomerID,"
                                + "OrderID = :OrderID,PartID = :PartID,Status = :Status,PartNo = :PartNo,Remark = " +
                                ":Remark,"
                                + "BackTime = :BackTime,SubmitTime = now(),StatusText=:StatusText," +
                                "SendStatus=:SendStatus,SendDesc=:SendDesc,Type=:Type,DemandID=:DemandID," +
                                "TechChangeID=:TechChangeID,BOMType=:BOMType WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wMESMaterialReturn.ID);
            wParamMap.put("Code", wMESMaterialReturn.Code);
            wParamMap.put("UpFlowID", wMESMaterialReturn.UpFlowID);
            wParamMap.put("FlowType", wMESMaterialReturn.FlowType);
            wParamMap.put("FlowID", wMESMaterialReturn.FlowID);
            wParamMap.put("FollowerID", StringUtils.Join(",", wMESMaterialReturn.FollowerID));
            wParamMap.put("Factory", wMESMaterialReturn.Factory);
            wParamMap.put("AsnType", wMESMaterialReturn.AsnType);
            wParamMap.put("CreateTime", wMESMaterialReturn.CreateTime);
            wParamMap.put("CreateTime1", wMESMaterialReturn.CreateTime1);
            wParamMap.put("MonitorNo", wMESMaterialReturn.MonitorNo);
            wParamMap.put("Monitor", wMESMaterialReturn.Monitor);
            wParamMap.put("ProductID", wMESMaterialReturn.ProductID);
            wParamMap.put("LineID", wMESMaterialReturn.LineID);
            wParamMap.put("CustomerID", wMESMaterialReturn.CustomerID);
            wParamMap.put("OrderID", wMESMaterialReturn.OrderID);
            wParamMap.put("PartID", wMESMaterialReturn.PartID);
            wParamMap.put("Status", wMESMaterialReturn.Status);
            wParamMap.put("PartNo", wMESMaterialReturn.PartNo);
            wParamMap.put("Remark", wMESMaterialReturn.MyRemark);
            wParamMap.put("BackTime", wMESMaterialReturn.BackTime);
            wParamMap.put("SubmitTime", wMESMaterialReturn.SubmitTime);
            wParamMap.put("StatusText", wMESMaterialReturn.StatusText);
            wParamMap.put("SendStatus", wMESMaterialReturn.SendStatus);
            wParamMap.put("SendDesc", wMESMaterialReturn.SendDesc);
            wParamMap.put("Type", wMESMaterialReturn.Type);
            wParamMap.put("DemandID", wMESMaterialReturn.DemandID);
            wParamMap.put("TechChangeID", wMESMaterialReturn.TechChangeID);
            wParamMap.put("BOMType", wMESMaterialReturn.BOMType);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wMESMaterialReturn.getID() <= 0) {
                wMESMaterialReturn.setID(Objects.requireNonNull(keyHolder.getKey()).intValue());
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wMESMaterialReturn;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<MESMaterialReturn> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (MESMaterialReturn wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.mes_materialreturn WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取最新的编码
     */
    public String GetNewCode(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser, MESDBSource.Basic, 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT Code FROM {0}.mes_materialreturn WHERE id IN( SELECT MAX(ID) FROM {0}.mes_materialreturn);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            int wNumber = 1;
            int wMonth = Calendar.getInstance().get(Calendar.MONTH) + 1;
            for (Map<String, Object> wReader : wQueryResult) {
                String wDemandNo = StringUtils.parseString(wReader.get("Code"));
                int wCodeMonth = StringUtils.parseInt(wDemandNo.substring(7, 9));
                if (wMonth > wCodeMonth)
                    wNumber = 1;
                else
                    wNumber = StringUtils.parseInt(wDemandNo.substring(10)) + 1;
            }

            wResult = StringUtils.Format("TLD{0}{1}{2}", String.valueOf(Calendar.getInstance().get(Calendar.YEAR)),
                    String.format("%02d", Calendar.getInstance().get(Calendar.MONTH) + 1),
                    String.format("%04d", wNumber));
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public MESMaterialReturn SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        MESMaterialReturn wResult = new MESMaterialReturn();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<MESMaterialReturn> wList = this.SelectList(wLoginUser, wID, -1, "", "", -1, -1, -1, -1, -1, null, null,
                    null, null, null, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
            wResult.ItemList = MESMaterialReturnItemDAO.getInstance().SelectList(wLoginUser, -1, wResult.ID, -1, -1, "",
                    wErrorCode);
            wResult.ItemList.forEach(p -> {
//                p.DemandNo = wList.get(0).Code;
                p.ProductNo = wList.get(0).ProductNo;
                p.LineName = wList.get(0).LineName;
                p.CustomerName = wList.get(0).CustomerName;
                p.PartNo = wList.get(0).PartNo;
                p.PartName = wList.get(0).PartName;
            });
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<MESMaterialReturn> SelectList(BMSEmployee wLoginUser, int wID, int wUpFlowID, String wAsnType,
                                              String wCode, int wProductID, int wLineID, int wCustomerID,
                                              int wOrderID, int wPartID,
                                              List<Integer> wStatusList, Calendar wStartTime, Calendar wEndTime,
                                              List<Integer> wTaskIDList,
                                              List<Integer> wNotStatusList, OutResult<Integer> wErrorCode) {
        List<MESMaterialReturn> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            if (wTaskIDList == null) {
                wTaskIDList = new ArrayList<>();
            }

            if (wStatusList == null) {
                wStatusList = new ArrayList<>();
            }

            wStatusList.removeIf(p -> p == -1);

            if (wNotStatusList == null) {
                wNotStatusList = new ArrayList<>();
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.ProductNo ProductNo,t2.Name LineName,t3.CustomerName CustomerName,"
                            + "t3.CustomerCode CustomerCode,t4.Name PartName,t4.Code PartCode,t5.Name Creator,t6" +
                            ".DemandNo,t7.Code TechChangeNo  "
                            + "FROM {0}.mes_materialreturn t " + "left join {0}.fpc_product t1 on t.ProductID=t1.ID  "
                            + "left join {0}.fmc_line t2 on t.LineID=t2.ID  "
                            + "left join {0}.crm_customer t3 on t.CustomerID=t3.ID  "
                            + "left join {0}.fpc_part t4 on t.PartID=t4.ID  left join {0}.mbs_user t5 on t" +
                            ".UpFlowID=t5.ID "
                            + " left join {0}.wms_pickdemand t6 on t.DemandID=t6.ID "
                            + " left join {0}.tcm_techchangenotice t7 on t.TechChangeID=t7.ID "
                            + " WHERE  1=1  " + "and ( :wID <= 0 or :wID = t.ID ) "
                            + " and ( :wAsnType is null or :wAsnType = '''' or :wAsnType = t.AsnType ) "
                            + " and ( :wCode is null or :wCode = '''' or :wCode = t.Code ) "
                            + " and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  t" +
                            ".SubmitTime ) "
                            + " and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  t" +
                            ".CreateTime ) "
                            + " and ( :wProductID <= 0 or :wProductID = t.ProductID ) and ( :wLineID <= 0 or :wLineID" +
                            " = t.LineID ) "
                            + " and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                            + " and ( :wOrderID <= 0 or :wOrderID = t.OrderID ) "
                            + " and ( :wPartID <= 0 or :wPartID = t.PartID ) "
                            + " and ( :wUpFlowID <= 0 or :wUpFlowID = t.UpFlowID ) "
                            + " and ( :wIDs is null or :wIDs = '''' or t.ID in ({1})) "
                            + " and ( :wNotStatus is null or :wNotStatus = '''' or t.Status not in ({3})) "
                            + " and ( :wStatus is null or :wStatus = '''' or t.Status in ({2})) ;",
                    wInstance.Result, wTaskIDList.size() > 0 ? StringUtils.Join(",", wTaskIDList) : "0",
                    wStatusList.size() > 0 ? StringUtils.Join(",", wStatusList) : "0",
                    wNotStatusList.size() > 0 ? StringUtils.Join(",", wNotStatusList) : "0");

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wAsnType", wAsnType);
            wParamMap.put("wCode", wCode);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wStatus", StringUtils.Join(",", wStatusList));
            wParamMap.put("wNotStatus", StringUtils.Join(",", wNotStatusList));
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wUpFlowID", wUpFlowID);
            wParamMap.put("wIDs", StringUtils.Join(",", wTaskIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            for (Map<String, Object> wReader : wQueryResult) {
                MESMaterialReturn wItem = new MESMaterialReturn();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.Code = StringUtils.parseString(wReader.get("Code"));
                wItem.UpFlowID = StringUtils.parseInt(wReader.get("UpFlowID"));
                wItem.FlowType = StringUtils.parseInt(wReader.get("FlowType"));
                wItem.FlowID = StringUtils.parseInt(wReader.get("FlowID"));
                wItem.FollowerID = StringUtils
                        .parseIntList(StringUtils.parseString(wReader.get("FollowerID")).split(","));
                wItem.Factory = StringUtils.parseString(wReader.get("Factory"));
                wItem.AsnType = StringUtils.parseString(wReader.get("AsnType"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.CreateTime1 = StringUtils.parseCalendar(wReader.get("CreateTime1"));
                wItem.MonitorNo = StringUtils.parseString(wReader.get("MonitorNo"));
                wItem.Monitor = StringUtils.parseString(wReader.get("Monitor"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.MyRemark = StringUtils.parseString(wReader.get("Remark"));
                wItem.BackTime = StringUtils.parseCalendar(wReader.get("BackTime"));
                wItem.SubmitTime = StringUtils.parseCalendar(wReader.get("SubmitTime"));
                wItem.StatusText = StringUtils.parseString(wReader.get("StatusText"));
                wItem.SendStatus = StringUtils.parseInt(wReader.get("SendStatus"));
                wItem.SendDesc = StringUtils.parseString(wReader.get("SendDesc"));
                if (StringUtils.isEmpty(wItem.StatusText)) {
                    wItem.StatusText = MESMaterialReturnStatus.getEnumType(wItem.Status).getLable();
                }

                // ①辅助属性
                wItem.AsnTypeText = MESAsnType.getEnumType(StringUtils.parseInt(wItem.AsnType)).getLable();
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.CustomerCode = StringUtils.parseString(wReader.get("CustomerCode"));
                wItem.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.Creator = StringUtils.parseString(wReader.get("Creator"));
                wItem.Type = StringUtils.parseInt(wReader.get("Type"));
                wItem.BOMType = StringUtils.parseInt(wReader.get("BOMType"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.TechChangeID = StringUtils.parseInt(wReader.get("TechChangeID"));
                wItem.TechChangeNo = StringUtils.parseString(wReader.get("TechChangeNo"));
                wItem.UpFlowName = wItem.Creator;
                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";
                wItem.CreateTime1Text = wItem.CreateTime1.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime1.getTime())
                        : "";
                wItem.BackTimeText = wItem.BackTime.compareTo(wBaseTime1) > 0 ? wSDF.format(wItem.BackTime.getTime())
                        : "";

                wItem.RMaNO = wItem.Code;

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    @Override
    public List<BPMTaskBase> BPM_GetUndoTaskList(BMSEmployee wLoginUser, int wResponsorID,
                                                 OutResult<Integer> wErrorCode) {
        List<MESMaterialReturn> wResult = new ArrayList<>();
        try {
            // 获取所有任务消息
            List<BFCMessage> wMessageList = CoreServiceImpl.getInstance()
                    .BFC_GetMessageList(wLoginUser, wLoginUser.getID(), BPMEventModule.ReturnToWarehouse.getValue(), -1,
                            BFCMessageType.Task.getValue(), 0, -1, null, null)
                    .List(BFCMessage.class);
            wMessageList.addAll(CoreServiceImpl.getInstance().BFC_GetMessageList(wLoginUser, wLoginUser.getID(),
                            BPMEventModule.ReturnToWarehouse.getValue(), -1, BFCMessageType.Task.getValue(), 1, -1,
                            null, null)
                    .List(BFCMessage.class));
            wMessageList.addAll(CoreServiceImpl.getInstance().BFC_GetMessageList(wLoginUser, wLoginUser.getID(),
                            BPMEventModule.ReturnToWarehouse.getValue(), -1, BFCMessageType.Task.getValue(), 2, -1,
                            null, null)
                    .List(BFCMessage.class));

            List<Integer> wTaskIDList = wMessageList.stream().map(p -> (int) p.MessageID).distinct()
                    .collect(Collectors.toList());

            // 所有未完成的任务
            Map<Integer, MESMaterialReturn> wTaskMap = new HashMap<>();
            if (wTaskIDList.size() > 0) {
                List<MESMaterialReturn> wMTCTaskListTemp = this.SelectList(wLoginUser, -1, -1, "", "", -1, -1, -1, -1,
                        -1, null, null, null, wTaskIDList, null, wErrorCode);

                wTaskMap = wMTCTaskListTemp.stream().collect(Collectors.toMap(p -> p.ID, p -> p, (o1, o2) -> o1));

            }
            MESMaterialReturn wTaskTemp = null;
            for (BFCMessage wBFCMessage : wMessageList) {
                if (!wTaskMap.containsKey((int) wBFCMessage.getMessageID()))
                    continue;

                wTaskTemp = CloneTool.Clone(wTaskMap.get((int) wBFCMessage.getMessageID()), MESMaterialReturn.class);
                wTaskTemp.StepID = wBFCMessage.getStepID();
                wResult.add(wTaskTemp);
            }

            wResult.sort(Comparator.comparing(MESMaterialReturn::getSubmitTime).reversed());
            // 剔除任务状态为0的任务（废弃任务）
            if (wResult.size() > 0) {
                wResult = wResult.stream().filter(p -> p.Status != 0).collect(Collectors.toList());
            }
        } catch (Exception e) {
            wErrorCode.set(MESException.Exception.getValue());
            logger.error(e.toString());
        }
        return new ArrayList<>(wResult);
    }

    @Override
    public List<BPMTaskBase> BPM_GetDoneTaskList(BMSEmployee wLoginUser, int wResponsorID, Calendar wStartTime,
                                                 Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<MESMaterialReturn> wResult = new ArrayList<>();
        wErrorCode.set(0);
        try {
            List<MESMaterialReturn> wTaskList = new ArrayList<>();
            // 获取所有任务消息
            List<BFCMessage> wMessageList = CoreServiceImpl.getInstance()
                    .BFC_GetMessageList(wLoginUser, wLoginUser.getID(), BPMEventModule.ReturnToWarehouse.getValue(), -1,
                            BFCMessageType.Task.getValue(), BFCMessageStatus.Finished.getValue(), -1, wStartTime,
                            wEndTime)
                    .List(BFCMessage.class);
            wMessageList.addAll(CoreServiceImpl.getInstance()
                    .BFC_GetMessageList(wLoginUser, wLoginUser.getID(), BPMEventModule.ReturnToWarehouse.getValue(), -1,
                            BFCMessageType.Task.getValue(), BFCMessageStatus.Close.getValue(), -1, wStartTime, wEndTime)
                    .List(BFCMessage.class));

            List<Integer> wTaskIDList = wMessageList.stream().map(p -> (int) p.MessageID).distinct()
                    .collect(Collectors.toList());

            if (wTaskIDList.size() <= 0) {
                return new ArrayList<>(wResult);
            }

            wTaskList = this.SelectList(wLoginUser, -1, -1, "", "", -1, -1, -1, -1, -1, null, wStartTime, wEndTime,
                    wTaskIDList, null, wErrorCode);

            wTaskList.sort(Comparator.comparing(MESMaterialReturn::getSubmitTime).reversed());

            wResult = wTaskList;
            // 剔除任务状态为0的任务（废弃任务）
            if (wResult.size() > 0) {
                wResult = wResult.stream().filter(p -> p.Status != 0).collect(Collectors.toList());
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return new ArrayList<>(wResult);
    }

    @Override
    public List<BPMTaskBase> BPM_GetSendTaskList(BMSEmployee wLoginUser, int wResponsorID, Calendar wStartTime,
                                                 Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<MESMaterialReturn> wResult = new ArrayList<>();
        try {
            wResult = this.SelectList(wLoginUser, -1, wResponsorID, "", "", -1, -1, -1, -1, -1, null, wStartTime,
                    wEndTime, null, null, wErrorCode);
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return new ArrayList<>(wResult);
    }

    @Override
    public BPMTaskBase BPM_UpdateTask(BMSEmployee wLoginUser, BPMTaskBase wTask, OutResult<Integer> wErrorCode) {
        BPMTaskBase wResult = new BPMTaskBase();
        try {
            wResult = this.Update(wLoginUser, (MESMaterialReturn) wTask, wErrorCode);
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return wResult;
    }

    @Override
    public BPMTaskBase BPM_GetTaskInfo(BMSEmployee wLoginUser, int wTaskID, String wCode,
                                       OutResult<Integer> wErrorCode) {
        MESMaterialReturn wResult = new MESMaterialReturn();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser, MESDBSource.APS, 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<MESMaterialReturn> wList = this.SelectList(wLoginUser, wTaskID, -1, "", wCode, -1, -1, -1, -1, -1,
                    null, null, null, null, null, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 获取工位退料WMS数据
     */
    public WMSLinePartTLs GetWMSLinePartTLs(MESMaterialReturn wMESMaterialReturn,
                                            List<MESMaterialReturnItem> wItemList) {
        WMSLinePartTLs wResult = null;
        OutResult<Integer> wErrorCode = new OutResult<>(0);
        try {
            List<WMSLinePartTL> wWMSLinePartTLList = new ArrayList<>();

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String wCurrentTime = wSDF.format(wMESMaterialReturn.CreateTime.getTime());

            String monitorNo = "";
            String monitor = "";
            BMSEmployee wUser = WMSPickDemandDAO.getInstance().GetMonitorByPart(BaseDAO.SysAdmin,
                    wMESMaterialReturn.PartID, wErrorCode);
            if (StringUtils.isNotEmpty(wUser.LoginID)) {
                monitorNo = wUser.LoginID;
                monitor = wUser.Name;
            }

            WMSLinePartTL wWMSLinePartTL = new WMSLinePartTL();
            wWMSLinePartTL.warehouseId = "WH01";
            wWMSLinePartTL.customerId = "1900";
            wWMSLinePartTL.asnType = "105";
            wWMSLinePartTL.docNo = wMESMaterialReturn.Code;
            wWMSLinePartTL.asnReferenceA = wMESMaterialReturn.DemandNo;
            wWMSLinePartTL.expectedArriveTime1 = wCurrentTime;
            wWMSLinePartTL.expectedArriveTime2 = wCurrentTime;
            wWMSLinePartTL.supplierId = monitorNo;
            wWMSLinePartTL.supplierName = monitor;
            wWMSLinePartTL.hedi01 = wMESMaterialReturn.ProductNo;
            wWMSLinePartTL.hedi02 = wMESMaterialReturn.LineName;
            wWMSLinePartTL.hedi03 = wMESMaterialReturn.CustomerCode;
            wWMSLinePartTL.hedi04 = wMESMaterialReturn.PartNo;
            wWMSLinePartTL.hedi05 = wMESMaterialReturn.PartName;
            wWMSLinePartTL.hedi06 = wMESMaterialReturn.PartCode;
            wWMSLinePartTL.hedi07 = String.valueOf(wMESMaterialReturn.BOMType);
            wWMSLinePartTL.hedi08 = String.valueOf(wMESMaterialReturn.OrderID);

            wWMSLinePartTL.details = new ArrayList<>();
//            int wBOMType = 2;
            WMSPickDemandItemStock wWMSPickDemandItemStock;
            for (MESMaterialReturnItem wMESMaterialReturnItem : wItemList) {
                if (wMESMaterialReturnItem.GoodNumber <= 0) {
                    continue;
                }

                wWMSPickDemandItemStock = new WMSPickDemandItemStock();

                //查询出库详情
                if (wMESMaterialReturnItem.ReturnListID > 0) {//工艺变更，不选批次
                    List<WMSPickDemandItemStock> wmsPickDemandItemStocks =
                            WMSPickDemandItemStockDAO.getInstance().SelectList(BaseDAO.SysAdmin, -1,
                                    wMESMaterialReturnItem.DemandItemID, wMESMaterialReturnItem.BatchNo,
                                    "", wErrorCode);
                    if (wmsPickDemandItemStocks != null && wmsPickDemandItemStocks.size() > 0) {
                        wWMSPickDemandItemStock = wmsPickDemandItemStocks.get(0);
                    }
                } else {//主动退料
                    if (wMESMaterialReturnItem.ItemStockID <= 0) {
                        logger.info("主动退料：明细未赋值ItemStockID");
                        continue;
                    }

                    wWMSPickDemandItemStock = WMSPickDemandItemStockDAO.getInstance().SelectByID(BaseDAO.SysAdmin,
                            wMESMaterialReturnItem.ItemStockID, wErrorCode);
                }

                if (wWMSPickDemandItemStock == null || wWMSPickDemandItemStock.ID <= 0) {
                    logger.info("主动退料：未查询到批次出库信息");
                    continue;
                }

//                if (wMESMaterialReturnItem.APSBOMItemNum > 0) {
//                    List<APSBOMItem> apsbomItems = APSBOMItemDAO.getInstance().APS_QueryBOMItemList(BaseDAO.SysAdmin,
//                            -1, wMESMaterialReturn.OrderID, "",
//                            "", -1, -1, -1, wMESMaterialReturn.PartID, wMESMaterialReturnItem.PartPointID,
//                            wMESMaterialReturnItem.MaterialID, "", -1, -1, -1, null, -1, -1, -1, wErrorCode);
//                    if (apsbomItems != null && apsbomItems.size() > 0) {
//                        // BOM类型
//                        wBOMType = apsbomItems.get(0).BOMType;
//                    }
//                }

                WMSTLDetail wWMSTLDetail = new WMSTLDetail();

                wWMSTLDetail.sku = wMESMaterialReturnItem.MaterialNo;
                wWMSTLDetail.expectedQty = wMESMaterialReturnItem.GoodNumber;
                wWMSTLDetail.lotAtt01 = CalendarUtil.FormatConversion(wMESMaterialReturnItem.ProductTime,
                        "yyyy-MM-dd");
                wWMSTLDetail.lotAtt02 = wWMSPickDemandItemStock.ExpirationDate;
                wWMSTLDetail.lotAtt03 = wWMSPickDemandItemStock.WarehousingDate;
                wWMSTLDetail.lotAtt04 = wWMSPickDemandItemStock.Stock;
                wWMSTLDetail.lotAtt05 = wWMSPickDemandItemStock.Supplier;
                wWMSTLDetail.lotAtt06 = "";
                wWMSTLDetail.lotAtt07 = wWMSPickDemandItemStock.ProductName;
                wWMSTLDetail.lotAtt08 = wWMSPickDemandItemStock.ProjectStockNo;
                wWMSTLDetail.lotAtt09 = "";
                wWMSTLDetail.lotAtt10 = wWMSPickDemandItemStock.SerialNo;
                wWMSTLDetail.lotAtt11 = wWMSPickDemandItemStock.AssessmentType;
                wWMSTLDetail.lotAtt12 = wWMSPickDemandItemStock.SupplierLotNumber;
                wWMSTLDetail.dedi01 = wMESMaterialReturnItem.PartPointCode;
                wWMSTLDetail.dedi02 = wMESMaterialReturnItem.PartPointName;
                wWMSTLDetail.dedi03 = wMESMaterialReturnItem.RowNo;
                wWMSTLDetail.dedi04 = String.valueOf(wMESMaterialReturnItem.APSBOMItemNum);
                wWMSTLDetail.dedi05 = wWMSPickDemandItemStock.BatchNumber;

                wWMSLinePartTL.details.add(wWMSTLDetail);
            }

//            wWMSLinePartTL.hedi07 = String.valueOf(wBOMType);
            wWMSLinePartTLList.add(wWMSLinePartTL);

            wResult = new WMSLinePartTLs(wWMSLinePartTLList);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取退料清单导出数据集合
     */
    public MyExcelSheet GetMyExcelSheet(MESMaterialReturn wMESMaterialReturn, List<MESMaterialReturnItem> wItemList) {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            wResult.SheetName = "退库物料清单";
            wResult.TitleName = wResult.SheetName;
            wResult.HeaderList = new ArrayList<>(Arrays.asList("工位编码", "工位名称", "工序名称", "物料编码", "物料名称", "单位", "数量", "车型",
                    "车号", "项目号", "必换/偶换", "必修/偶修", "评估类型", "退库原因"));
            wResult.DataList = new ArrayList<>();
            for (MESMaterialReturnItem wMESMaterialReturnItem : wItemList) {
                List<String> wValueList = new ArrayList<>();
                wValueList.add(wMESMaterialReturn.PartCode);
                wValueList.add(wMESMaterialReturn.PartName);
                wValueList.add(wMESMaterialReturnItem.PartPointName);
                wValueList.add(wMESMaterialReturnItem.MaterialNo);
                wValueList.add(wMESMaterialReturnItem.MaterialName);
                wValueList.add(wMESMaterialReturnItem.UnitText);
                wValueList.add(String.valueOf(wMESMaterialReturnItem.RFQTY));
                wValueList.add(wMESMaterialReturn.ProductNo);
                if (wMESMaterialReturn.PartNo.contains("#")) {
                    wMESMaterialReturn.PartNo = wMESMaterialReturn.PartNo.split("#")[1];
                }
                wValueList.add(wMESMaterialReturn.PartNo);
                wValueList.add(wMESMaterialReturnItem.WBSNo);
                wValueList.add(wMESMaterialReturnItem.ReplaceTypeText);
                wValueList.add(wMESMaterialReturnItem.OutSourceTypeText);
                wValueList.add(wMESMaterialReturnItem.AssessmentType);
                wValueList.add(wMESMaterialReturnItem.BackReason);
                wResult.DataList.add(wValueList);
            }
        } catch (Exception e) {
            logger.error("GetMyExcelSheet:", e);
        }
        return wResult;
    }

    public double GetTotalReturnNubmerByPickDemandItemID(BMSEmployee wLoginUser, int wPickDemandItemID,
                                                         OutResult<Integer> wErrorCode) {
        double wResult = 0.0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT SUM(RFQTY) FQTY FROM {0}.`mes_materialreturnitem` t LEFT JOIN {0}.`mes_materialreturn` t1" +
                            " ON t.ReturnID=t1.ID WHERE t.demanditemid=:demanditemid AND t1.Status NOT IN (0,22,23);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("demanditemid", wPickDemandItemID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseDouble(wReader.get("FQTY"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }


    /**
     * 判断退料的数量问题
     */
    public String WMS_CheckNumberIsOk(BMSEmployee wLoginUser, List<MESMaterialReturnItem> wMESMaterialReturnItemList) {
        String wResult = "";
        try {
            OutResult<Integer> wErrorCode = new OutResult<Integer>(0);

            List<MESMaterialReturnItem> wList = wMESMaterialReturnItemList.stream()
                    .collect(Collectors.collectingAndThen(Collectors.toCollection(
                                    () -> new TreeSet<>(Comparator.comparing(o -> o.getMaterialID() + ";" + o.getPartPointID()))),
                            ArrayList::new));
            List<WMSPickDemandItemStock> wmsPickDemandItemStocks;
            double wTotalFQTY = 0.0;
            for (MESMaterialReturnItem wMESMaterialReturnItem : wList) {
                double wTotalNumber = this.GetTotalReturnNubmerByPickDemandItemID(wLoginUser,
                        wMESMaterialReturnItem.DemandItemID, wErrorCode);

                //计算出库总数
                wmsPickDemandItemStocks =
                        WMSPickDemandItemStockDAO.getInstance().SelectList(wLoginUser, -1,
                                wMESMaterialReturnItem.DemandItemID, "", "", wErrorCode);
                wTotalFQTY = wmsPickDemandItemStocks.stream().mapToDouble(p -> p.PickedNumber).sum();

                List<MESMaterialReturnItem> wTempList =
                        wMESMaterialReturnItemList.stream().filter(p -> p.PartPointID == wMESMaterialReturnItem.PartPointID
                                && p.MaterialID == wMESMaterialReturnItem.MaterialID).collect(Collectors.toList());
                if (wTempList.stream().mapToDouble(p -> p.RFQTY).sum() + wTotalNumber > wTotalFQTY) {
                    wResult = StringUtils.Format("物料【{0}】已发起退料申请，退料数量已超出库数量!", wMESMaterialReturnItem.MaterialNo);
                    return wResult;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<BFCMessage> WMS_QueryTechMessageList(BMSEmployee wLoginUser, int wTagType, int wModuleID,
                                                     Calendar wStartTime, Calendar wEndTime,
                                                     OutResult<Integer> wErrorCode) {
        List<BFCMessage> wResult = new ArrayList<BFCMessage>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }

            if (wTagType == 1) {
                //待办，近一年的时间
                Calendar wSTime = Calendar.getInstance();
                wSTime.add(Calendar.YEAR, -1);
                Calendar wETime = Calendar.getInstance();
                wETime.add(Calendar.DATE, 1);

                wResult = CoreServiceImpl.getInstance().BFC_GetMessageListByCondition(wLoginUser, -1,
                        -1, BFCMessageType.Task.getValue(), StringUtils.parseListArgs(wModuleID), -1, -1,
                        StringUtils.parseListArgs(0, 1, 2), -1, -1,
                        wSTime, wETime).List(BFCMessage.class);
            } else if (wTagType == 2) {
                wResult = CoreServiceImpl.getInstance().BFC_GetMessageListByCondition(wLoginUser, -1,
                        -1, BFCMessageType.Task.getValue(), StringUtils.parseListArgs(wModuleID), -1, -1,
                        StringUtils.parseListArgs(3, 4), -1, -1,
                        wStartTime, wEndTime).List(BFCMessage.class);
            }

            //翻译人员
            if (wResult.size() > 0) {
                for (BFCMessage wBFCMessage : wResult) {
                    wBFCMessage.Responsor = APSConstans.GetBMSEmployeeName((int) wBFCMessage.ResponsorID);
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询BOM类型
     */
    public int QueryBOMType(BMSEmployee wLoginUser, int orderID, int demandItemID, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT BOMType FROM {0}.`aps_bomitem` WHERE OrderID=:OrderID AND " +
                            "OrderNum IN (SELECT BOMItemNum FROM iplantmes.`wms_pickdemanditem` WHERE ID=:ID);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("OrderID", orderID);
            wParamMap.put("ID", demandItemID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("BOMType"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
